﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using ContactManager.Common;
using ContactManager.Entity;

namespace ContactManager
{
    public partial class IncFrm : AlSkin.AlForm.AlBaseForm
    {

        private string m_date_sql = "";//时间条件sql由incMonthFrm 传入

        public IncFrm()
        {
            InitializeComponent();
        }

        public IncFrm(string sql)
        {
            this.m_date_sql = sql;
            InitializeComponent();
        }

        private void IncFrm_Load(object sender, EventArgs e)
        {
            this.labelContactName.Text = Constant.TITLE + GlobleUser.USER_NAME;

            //初始化comb
            this.combType.Items.Clear();
            this.combPayType.Items.Clear();
            string combTypeSql = "select * from typeinc";
            DataSet typeDs = AccessHelper.ExecuteQuery(combTypeSql);
            foreach (DataRow dr in typeDs.Tables[0].Rows)
            {
                TypeInc ti = new TypeInc();
                ti.Id = dr[0].ToString();
                ti.Name = dr[1].ToString();
                ti.Inpay = dr[2].ToString();
                if ("0".Equals(ti.Inpay))
                {
                    this.combType.Items.Add(ti);
                }
                else
                {
                    this.combPayType.Items.Add(ti);
                }
            }

            //初始化表单
            this.dtpDate.Value = DateTime.Now;
            this.dtpPayDate.Value = DateTime.Now;


            labSumInc.Text = "";
            labSumPay.Text = "";
            labSumTotal.Text = "";

            initIncInForm();
            initPayInForm();
            

            //初始化最近联系人
            this.combRecent.Items.Clear();
            string recentSql = "select top 18 c.c_id,c.c_name,max(t.t_time) from contacters c,traces t where c.c_id = t.t_c_id group by c.c_id,c.c_name order by max(t.t_time) desc";
            DataSet recentDs = AccessHelper.ExecuteQuery(recentSql);
            foreach (DataRow dr in recentDs.Tables[0].Rows)
            {
                ComboBoxItem ci = new ComboBoxItem();
                ci.Text = Convert.ToString(dr["c_name"]);
                ci.Value = dr["c_id"];
                this.combRecent.Items.Add(ci);
            }
            //初始化统计comb
            initSumComb();
            //初始化m_date_sql
            if (m_date_sql.Length == 0)
            {
                m_date_sql = " and i_date >=#" + this.dtpSumStart.Value.ToString("yyyy/MM/dd") + "# and i_date <=#" + this.dtpSumEnd.Value.ToString("yyyy/MM/dd") + "# order by i_date desc";
            }
            //初始化收入表
            initIncDataGrid(m_date_sql);
            //初始化支出表
            initPayDataGrid(m_date_sql);
            //统计
            getSum(m_date_sql.Replace("order by i_date desc",""));
        }

        private void initSumComb()
        {
            //初始化统计comb
            DateTime now = DateTime.Now;
            DateTime monthFistDate = new DateTime(now.Year, now.AddMonths(-3).Month, now.Day);
            DateTime monthLastDate = now;
            this.dtpSumStart.Value = monthFistDate;
            this.dtpSumEnd.Value = monthLastDate;
        }

        private void initPayDataGrid(string sql)
        {
            this.dataGridViewPay.Rows.Clear();
            string paySql = "select i.i_id,i.i_date,i.i_iorp,t.t_name,i.i_num_one,i.i_explain_one,i.i_num_second,i.i_explain_second,i.i_name,t.t_id " +
                           "from incpay i,typeinc t " +
                           "where i.i_type_id = t.t_id and i.i_iorp = 1 "+sql;
            DataSet payds = AccessHelper.ExecuteQuery(paySql);
            foreach (DataRow dr in payds.Tables[0].Rows)
            {
                string id = dr[0].ToString();
                string date = dr[1].ToString();
                string iorp = dr[2].ToString();
                string type = dr[3].ToString();
                string num1 = dr[4].ToString();
                string explain1 = dr[5].ToString();
                string num2 = dr[6].ToString();
                string explain2 = dr[7].ToString();
                string name = dr[8].ToString();
                string typeid = dr[9].ToString();
                this.dataGridViewPay.Rows.Add(id, date, iorp, type, num1, explain1, num2, explain2, name, typeid);
            }
        }

        private void initIncDataGrid(string sql)
        {
            this.dataGridViewIncome.Rows.Clear();
            string inSql = "select i.i_id,i.i_date,i.i_iorp,t.t_name,i.i_num_one,i.i_explain_one,i.i_num_second,i.i_explain_second,i.i_name,t.t_id " +
                           "from incpay i,typeinc t " +
                           "where i.i_type_id = t.t_id and i.i_iorp = 0"+sql;
            DataSet inds = AccessHelper.ExecuteQuery(inSql);
            foreach (DataRow dr in inds.Tables[0].Rows)
            {
                string id = dr[0].ToString();
                string date = dr[1].ToString();
                string iorp = dr[2].ToString();
                string type = dr[3].ToString();
                string num1 = dr[4].ToString();
                string explain1 = dr[5].ToString();
                string num2 = dr[6].ToString();
                string explain2 = dr[7].ToString();
                string name = dr[8].ToString();
                string typeid = dr[9].ToString();
                this.dataGridViewIncome.Rows.Add(id, date, iorp, type, num1, explain1, num2, explain2, name, typeid);
            }
        }

        private void initIncInForm(Boolean flag)
        {
            this.dtpDate.Enabled = flag;
            this.combType.Enabled = flag;
            this.txtNumOne.Enabled = flag;
            this.txtExplainOne.Enabled = flag;
            this.txtNumSecond.Enabled = flag;
            this.txtExplainSecond.Enabled = flag;
            this.txtName.Enabled = flag;
        }

        private void initIncInForm()
        {
            this.dtpDate.Value = DateTime.Now;
            this.txtNumOne.Clear();
            this.txtExplainOne.Clear();
            this.txtNumSecond.Clear();
            this.txtExplainSecond.Clear();
            this.txtName.Clear();
            this.combType.SelectedIndex = -1;
        }

        private void initPayInForm(Boolean flag)
        {
            this.dtpPayDate.Enabled = flag;
            this.combPayType.Enabled = flag;
            this.txtPayNumOne.Enabled = flag;
            this.txtPayExplainOne.Enabled = flag;
            this.txtPayNumSecond.Enabled = flag;
            this.txtPayExplainSecond.Enabled = flag;
            this.txtPayName.Enabled = flag;
        }

        private void initPayInForm()
        {
            this.dtpPayDate.Value = DateTime.Now;
            this.txtPayNumOne.Clear();
            this.txtPayExplainOne.Clear();
            this.txtPayNumSecond.Clear();
            this.txtPayExplainSecond.Clear();
            this.txtPayName.Clear();
            this.combPayType.SelectedIndex = -1;
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpDate.Value;
            TypeInc ti = (TypeInc)this.combType.SelectedItem;
            string num1 = this.txtNumOne.Text.Trim();
            string explain1 = this.txtExplainOne.Text.Trim();
            string num2 = this.txtNumSecond.Text.Trim();
            string explain2 = this.txtExplainSecond.Text.Trim();
            string name = this.txtName.Text.Trim();

            if (this.combType.Enabled == true)
            { 
                //判断非编辑状态
                StringBuilder sb = new StringBuilder();
                if (explain1.Length != 0)
                {
                    sb.Append(" and i.i_explain_one like '%" + explain1 + "%'");
                }
                if (explain2.Length != 0)
                {
                    sb.Append(" and i.i_explain_second like '%" + explain2 + "%'");
                }
                if (name.Length != 0)
                {
                    sb.Append(" and i_name like '%" + name + "%'");
                }
                if (ti != null)
                {
                    sb.Append(" and i.i_type_id =" + ti.Id);
                }
                if (m_date_sql.Length != 0)
                {
                    sb.Append(m_date_sql);
                }

                initIncDataGrid(sb.ToString());
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpDate.Value;
            TypeInc ti = (TypeInc)this.combType.SelectedItem;
            string num1 = this.txtNumOne.Text.Trim();
            string explain1 = this.txtExplainOne.Text.Trim();
            string num2 = this.txtNumSecond.Text.Trim();
            string explain2 = this.txtExplainSecond.Text.Trim();
            string name = this.txtName.Text.Trim();

            if (this.combType.Enabled == true && DialogResult.Yes == MessageBox.Show("确认添加吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
            { 
                //判断非编辑状态

                if (this.txtNumOne.Text.Length == 0 && this.txtNumSecond.Text.Length == 0)
                {
                    MessageBox.Show("请输入收入或者贷入,必须填写一项", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (this.combType.Text.Length == 0)
                {
                    MessageBox.Show("请选择一个类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                string addSql = "insert into incpay(i_date,i_iorp,i_type_id,i_num_one,i_explain_one,i_num_second,i_explain_second,i_name) " +
                                "values(#" + date.ToString("yyyy/MM/dd HH:mm:ss") + "#,0,?,?,?,?,?,?)";
                OleDbParameter[] ps = { new OleDbParameter("@i_type_id",ti.Id),
                                        new OleDbParameter("@i_num_one",num1.Length == 0 ? "0" : num1),
                                        new OleDbParameter("@i_explain_one",explain1),
                                        new OleDbParameter("@i_num_second",num2.Length == 0 ? "0" : num2),
                                        new OleDbParameter("@i_explain_second",explain2),
                                        new OleDbParameter("@i_name",name)
                                      };
                if (AccessHelper.ExecuteNonQuery(addSql, ps) > 0)
                {
                    MessageBox.Show("保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.IncFrm_Load(this, null);
                    dtpSumStart_CloseUp(this, null);
                }
            }


        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (txtNumOne.Text.Trim().Length == 0)
            {
                MessageBox.Show("请先点击表格选择一条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            initIncInForm(true);
            this.btnQuery.Enabled = false;
            this.btnAdd.Enabled = false;
            this.btnOK.Enabled = true;
        }

        private void dataGridViewIncome_Click(object sender, EventArgs e)
        {
            if (this.dataGridViewIncome.CurrentRow != null) 
            {
                this.dtpDate.Value = Convert.ToDateTime(this.dataGridViewIncome.CurrentRow.Cells["i_date"].Value.ToString());
                this.txtNumOne.Text = this.dataGridViewIncome.CurrentRow.Cells["i_num_one"].Value.ToString();
                this.txtExplainOne.Text = this.dataGridViewIncome.CurrentRow.Cells["i_explain_one"].Value.ToString();
                this.txtNumSecond.Text = this.dataGridViewIncome.CurrentRow.Cells["i_num_second"].Value.ToString();
                this.txtExplainSecond.Text = this.dataGridViewIncome.CurrentRow.Cells["i_explain_second"].Value.ToString();
                this.txtName.Text = this.dataGridViewIncome.CurrentRow.Cells["i_name"].Value.ToString();
                string typeName = this.dataGridViewIncome.CurrentRow.Cells["i_type_id"].Value.ToString();

                //初始化comb
                this.combType.SelectedIndex = this.combType.FindStringExact(typeName);
                initIncInForm(false);
            }
            
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpDate.Value;
            TypeInc ti = (TypeInc)this.combType.SelectedItem;
            string num1 = this.txtNumOne.Text.Trim();
            string explain1 = this.txtExplainOne.Text.Trim();
            string num2 = this.txtNumSecond.Text.Trim();
            string explain2 = this.txtExplainSecond.Text.Trim();
            string name = this.txtName.Text.Trim();


            if (this.txtNumOne.Text.Length == 0)
            {
                MessageBox.Show("请输入收入", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (this.txtNumSecond.Text.Length == 0)
            {
                MessageBox.Show("请输入贷入", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (this.combType.Text.Length == 0)
            {
                MessageBox.Show("请选择一个类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            string updateSql = "update incpay set i_date=#" + date.ToString("yyyy/MM/dd HH:mm:ss") + "#,i_iorp=0,i_type_id=?,i_num_one=?,i_explain_one=?,i_num_second=?,i_explain_second=?,i_name=? where i_id=?";
            OleDbParameter[] ps = { new OleDbParameter("@i_type_id",ti.Id),
                                        new OleDbParameter("@i_num_one",num1),
                                        new OleDbParameter("@i_explain_one",explain1),
                                        new OleDbParameter("@i_num_second",num2),
                                        new OleDbParameter("@i_explain_second",explain2),
                                        new OleDbParameter("@i_name",name),
                                        new OleDbParameter("@i_id",this.dataGridViewIncome.CurrentRow.Cells["i_id"].Value.ToString())
                                      };
            if (AccessHelper.ExecuteNonQuery(updateSql, ps) > 0)
            {
                MessageBox.Show("修改成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.IncFrm_Load(this, null);

                this.btnOK.Enabled = false;
                this.btnAdd.Enabled = true;
                this.btnQuery.Enabled = true;
            }
        }

        private void combRecent_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBoxItem recent = (ComboBoxItem)this.combRecent.SelectedItem;
            TracesForm tf = new TracesForm(recent.Value.ToString());
            tf.ShowDialog();
        }

        private void btnPayQuery_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpPayDate.Value;
            TypeInc ti = (TypeInc)this.combPayType.SelectedItem;
            string num1 = this.txtPayNumOne.Text.Trim();
            string explain1 = this.txtPayExplainOne.Text.Trim();
            string num2 = this.txtPayNumSecond.Text.Trim();
            string explain2 = this.txtPayExplainSecond.Text.Trim();
            string name = this.txtPayName.Text.Trim();

            if (this.combPayType.Enabled == true)
            { 
                //判断是否为编辑状态，非编辑状态才可查询
                StringBuilder sb = new StringBuilder();
                if (num1.Length != 0)
                {
                    sb.Append(" and i.i_num_one =" + num1);
                }
                if (num2.Length != 0)
                {
                    sb.Append(" and i.i_num_second =" + num2);
                }
                if (explain1.Length != 0)
                {
                    sb.Append(" and i.i_explain_one like '%" + explain1 + "%'");
                }
                if (explain2.Length != 0)
                {
                    sb.Append(" and i.i_explain_second like '%" + explain2 + "%'");
                }
                if (name.Length != 0)
                {
                    sb.Append(" and i_name like '%" + name + "%'");
                }
                if (ti != null)
                {
                    sb.Append(" and i.i_type_id =" + ti.Id);
                }
                if (m_date_sql.Length != 0)
                {
                    sb.Append(m_date_sql);
                }

                initPayDataGrid(sb.ToString());
            }


            
        }

        private void btnPayAdd_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpPayDate.Value;
            TypeInc ti = (TypeInc)this.combPayType.SelectedItem;
            string num1 = this.txtPayNumOne.Text.Trim();
            string explain1 = this.txtPayExplainOne.Text.Trim();
            string num2 = this.txtPayNumSecond.Text.Trim();
            string explain2 = this.txtPayExplainSecond.Text.Trim();
            string name = this.txtPayName.Text.Trim();

            if (this.combPayType.Enabled == true && DialogResult.Yes == MessageBox.Show("确认添加吗？","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Information))
            {
                if (this.txtPayNumOne.Text.Length == 0 && this.txtPayNumSecond.Text.Length == 0)
                {
                    MessageBox.Show("请输入支出或者借出，必须填写一项", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                
                if (this.combPayType.Text.Length == 0)
                {
                    MessageBox.Show("请选择一个类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                string addSql = "insert into incpay(i_date,i_iorp,i_type_id,i_num_one,i_explain_one,i_num_second,i_explain_second,i_name) " +
                                "values(#" + date.ToString("yyyy/MM/dd HH:mm:ss") + "#,1,?,?,?,?,?,?)";
                OleDbParameter[] ps = { new OleDbParameter("@i_type_id",ti.Id),
                                        new OleDbParameter("@i_num_one",num1.Length == 0?"0":num1),
                                        new OleDbParameter("@i_explain_one",explain1),
                                        new OleDbParameter("@i_num_second",num2.Length == 0?"0":num2),
                                        new OleDbParameter("@i_explain_second",explain2),
                                        new OleDbParameter("@i_name",name)
                                      };
                if (AccessHelper.ExecuteNonQuery(addSql, ps) > 0)
                {
                    MessageBox.Show("保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.IncFrm_Load(this, null);
                    dtpSumStart_CloseUp(this, null);
                }
            }
        }

        private void btnPayEdit_Click(object sender, EventArgs e)
        {
            if (txtPayNumOne.Text.Trim().Length == 0)
            {
                MessageBox.Show("请先点击表格选择一条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            this.btnPayAdd.Enabled = false;
            this.btnPayQuery.Enabled = false;
            this.btnPayOK.Enabled = true;
            initPayInForm(true);
        }

        private void dataGridViewPay_Click(object sender, EventArgs e)
        {
            if (this.dataGridViewPay.CurrentRow != null)
            {
                this.dtpPayDate.Value = Convert.ToDateTime(this.dataGridViewPay.CurrentRow.Cells["pi_date"].Value.ToString());
                this.txtPayNumOne.Text = this.dataGridViewPay.CurrentRow.Cells["pi_num_one"].Value.ToString();
                this.txtPayExplainOne.Text = this.dataGridViewPay.CurrentRow.Cells["pi_explain_one"].Value.ToString();
                this.txtPayNumSecond.Text = this.dataGridViewPay.CurrentRow.Cells["pi_num_second"].Value.ToString();
                this.txtPayExplainSecond.Text = this.dataGridViewPay.CurrentRow.Cells["pi_explain_second"].Value.ToString();
                this.txtPayName.Text = this.dataGridViewPay.CurrentRow.Cells["pi_name"].Value.ToString();
                string typeName = this.dataGridViewPay.CurrentRow.Cells["pi_type_id"].Value.ToString();

                //初始化comb
                this.combPayType.SelectedIndex = this.combPayType.FindStringExact(typeName);
                initPayInForm(false);
            }
            
        }

        private void btnPayOK_Click(object sender, EventArgs e)
        {
            DateTime date = this.dtpPayDate.Value;
            TypeInc ti = (TypeInc)this.combPayType.SelectedItem;
            string num1 = this.txtPayNumOne.Text.Trim();
            string explain1 = this.txtPayExplainOne.Text.Trim();
            string num2 = this.txtPayNumSecond.Text.Trim();
            string explain2 = this.txtPayExplainSecond.Text.Trim();
            string name = this.txtPayName.Text.Trim();


            if (this.dataGridViewPay.CurrentRow != null)
            {

                if (this.txtPayNumOne.Text.Length == 0)
                {
                    MessageBox.Show("请输入收入", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (this.txtPayNumSecond.Text.Length == 0)
                {
                    MessageBox.Show("请输入贷入", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (this.combPayType.Text.Length == 0)
                {
                    MessageBox.Show("请选择一个类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                string updateSql = "update incpay set i_date=#" + date.ToString("yyyy/MM/dd HH:mm:ss") + "#,i_iorp=1,i_type_id=?,i_num_one=?,i_explain_one=?,i_num_second=?,i_explain_second=?,i_name=? where i_id=?";
                OleDbParameter[] ps = { new OleDbParameter("@i_type_id",ti.Id),
                                        new OleDbParameter("@i_num_one",num1),
                                        new OleDbParameter("@i_explain_one",explain1),
                                        new OleDbParameter("@i_num_second",num2),
                                        new OleDbParameter("@i_explain_second",explain2),
                                        new OleDbParameter("@i_name",name),
                                        new OleDbParameter("@i_id",this.dataGridViewPay.CurrentRow.Cells["pi_id"].Value.ToString())
                                      };
                if (AccessHelper.ExecuteNonQuery(updateSql, ps) > 0)
                {
                    MessageBox.Show("修改成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.IncFrm_Load(this, null);
                    this.btnPayOK.Enabled = false;
                    this.btnPayQuery.Enabled = true;
                    this.btnPayAdd.Enabled = true;
                }
            }

        }
        
        //验证数字输入，只能输入数字和小数点
        private static void checkNumTextBox(object sender, KeyPressEventArgs e)
        {
            if (!(((e.KeyChar >= '0') && (e.KeyChar <= '9')) || e.KeyChar <= 31))
            {
                if (e.KeyChar == '.')
                {
                    if (((TextBox)sender).Text.Trim().IndexOf('.') > -1) e.Handled = true;
                }
                else e.Handled = true;
            }
            else
            {
                if (e.KeyChar <= 31)
                {
                    e.Handled = false;
                }

            }
        }

        private void txtNumOne_KeyPress(object sender, KeyPressEventArgs e)
        {
            checkNumTextBox(sender, e);
        }

        private void txtNumSecond_KeyPress(object sender, KeyPressEventArgs e)
        {
            checkNumTextBox(sender, e);
        }

        private void txtPayNumOne_KeyPress(object sender, KeyPressEventArgs e)
        {
            checkNumTextBox(sender, e);
        }

        private void txtPayNumSecond_KeyPress(object sender, KeyPressEventArgs e)
        {
            checkNumTextBox(sender, e);
        }

        private void dtpSumEnd_CloseUp(object sender, EventArgs e)
        {
            //修改m_date_sql
            m_date_sql = " and i_date >=#" + this.dtpSumStart.Value.ToString("yyyy/MM/dd") + "# and i_date <=#" + this.dtpSumEnd.Value.ToString("yyyy/MM/dd") + "# order by i_date desc";

            if (DateTime.Compare(this.dtpSumEnd.Value, this.dtpSumStart.Value) < 0)
            {
                MessageBox.Show("结束时间不能大于开始时间", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                initSumComb();
                return;
            }
            string dateSql = "and i_date >= #" + this.dtpSumStart.Value.ToString("yyyy/MM/dd HH:mm:ss") + "# and i_date <= #" + this.dtpSumEnd.Value.ToString("yyyy/MM/dd HH:mm:ss") + "# order by i_date desc";
            initIncDataGrid(dateSql);
            initPayDataGrid(dateSql);
            getSum(dateSql.Replace("order by i_date desc", ""));
        }

        private void dtpSumStart_CloseUp(object sender, EventArgs e)
        {
            //修改m_date_sql
            m_date_sql = " and i_date >=#" + this.dtpSumStart.Value.ToString("yyyy/MM/dd") + "# and i_date <=#" + this.dtpSumEnd.Value.ToString("yyyy/MM/dd") + "# order by i_date desc";

            if (DateTime.Compare(this.dtpSumEnd.Value, this.dtpSumStart.Value) < 0)
            {
                MessageBox.Show("结束时间不能大于开始时间", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                initSumComb();
                return;
            }
            string dateSql = "and i_date >= #" + this.dtpSumStart.Value.ToString("yyyy/MM/dd HH:mm:ss") + "# and i_date <= #" + this.dtpSumEnd.Value.ToString("yyyy/MM/dd HH:mm:ss") + "# order by i_date desc";
            initIncDataGrid(dateSql);
            initPayDataGrid(dateSql);
            getSum(dateSql.Replace("order by i_date desc", ""));
        }

        private void getSum(string sql)
        {
            string sumIncSql = "select iif(sum(t.i_num_one) is null,0,sum(t.i_num_one)) as sumone,iif(sum(t.i_num_one) is null,0,sum(t.i_num_second)) as sumsecond from incpay t where t.i_iorp = 0 " + sql;
            string sumPaySql = "select iif(sum(t.i_num_one) is null,0,sum(t.i_num_one)) as sumone,iif(sum(t.i_num_one) is null,0,sum(t.i_num_second)) as sumsecond from incpay t where t.i_iorp = 1 " + sql;
            int incSumOne = 0;
            int incSumSecond = 0;
            int paySumOne = 0;
            int paySumSecond = 0;
            DataSet incDs = AccessHelper.ExecuteQuery(sumIncSql);
            foreach (DataRow dr in incDs.Tables[0].Rows)
            {
                incSumOne =Convert.ToInt32(dr[0].ToString());
                incSumSecond = Convert.ToInt32(dr[1].ToString());
            }

            DataSet payDs = AccessHelper.ExecuteQuery(sumPaySql);
            foreach (DataRow dr in payDs.Tables[0].Rows)
            {
                paySumOne = Convert.ToInt32(dr[0].ToString());
                paySumSecond = Convert.ToInt32(dr[1].ToString());
            }

            this.labSumInc.Text = "当前收入总和：" + incSumOne.ToString() + "  贷入总和：" + incSumSecond.ToString();
            this.labSumPay.Text = "当前支出总和：" + paySumOne.ToString() + "  借出总和：" + paySumSecond.ToString();
            this.labSumTotal.Text = "当前收支结余总和：" + (incSumOne - paySumOne).ToString() + "  借贷结余总和：" + (incSumSecond - paySumSecond).ToString();

        }

        private void IncFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show("您确定要退出吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
            {
                HomeFrm.icf = null;
            }
            else
            {
                e.Cancel = true;
            }
        }

        private void DelIncToolStripMenuItem_Click(object sender, EventArgs e)
        {
            delIncPay(this.dataGridViewIncome);
        }

        private void DelPayToolStripMenuItem_Click(object sender, EventArgs e)
        {
            delIncPay(this.dataGridViewPay);
        }

        private void delIncPay(DataGridView dgv)
        {
            if (dgv.CurrentRow != null)
            {
                string delSql = "delete * from incpay where i_id = " + dgv.CurrentRow.Cells["i_id"].Value.ToString();
                if (DialogResult.Yes == MessageBox.Show("您确定要删除吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
                {
                    if (AccessHelper.ExecuteNonQuery(delSql) > 0)
                    {
                        MessageBox.Show("删除成功", "提示");
                        this.IncFrm_Load(this, null);
                    }
                    else
                    {
                        MessageBox.Show("删除失败,请重试", "提示");
                    }
                }
            }
        }

        private void btnMonth_Click(object sender, EventArgs e)
        {
            if (HomeFrm.imf == null)
            {
                HomeFrm.imf = new IncMonthFrm();
                HomeFrm.imf.Show();
            }
            else
            {
                HomeFrm.imf.Activate();
            }
        }

        private void btnYear_Click(object sender, EventArgs e)
        {
            if (HomeFrm.iyf == null)
            {
                HomeFrm.iyf = new IncYearFrm();
                HomeFrm.iyf.Show();
            }
            else
            {
                HomeFrm.iyf.Activate();
            }

        }

        private void btnPayReset_Click(object sender, EventArgs e)
        {
            initPayInForm();
            initPayInForm(true);
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            initIncInForm();
            initIncInForm(true);
        }

        

    }
}
